package dk.modified.radioonlineapp;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class CommentsDataSource {

	// Database fields
	MCrypt mcrypt = new MCrypt();
	private SQLiteDatabase database;
	private SQLiteDBHelper dbHelper;
	private String[] allColumns = { SQLiteDBHelper.COLUMN_ID, SQLiteDBHelper.COLUMN_NAME,
			SQLiteDBHelper.COLUMN_RADIOURL, SQLiteDBHelper.COLUMN_SLUG, SQLiteDBHelper.COLUMN_COUNTRY };

	// Constructor
	public CommentsDataSource(Context context) {
		dbHelper = new SQLiteDBHelper(context);
	}
	

	// Opens database
	public void Open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	// Closes database
	public void Close() {
		dbHelper.close();
	}

	// Adds the received radio to the radio table
	public void InsertRadioFromJSON(Radio radio) throws Exception {
		ContentValues values = new ContentValues();
		values.put(SQLiteDBHelper.COLUMN_ID, radio.getId());
		values.put(SQLiteDBHelper.COLUMN_NAME, radio.getName());
		values.put(SQLiteDBHelper.COLUMN_RADIOURL, radio.getUrl());
		values.put(SQLiteDBHelper.COLUMN_SLUG, radio.getSlug());
		values.put(SQLiteDBHelper.COLUMN_COUNTRY, radio.getCountry());
		database.insert(SQLiteDBHelper.TABLE_RADIOS, null, values);
	}

	// Selects all radios from the radio table, and returns them in a
	// list<Radio>
	public List<Radio> SelectRadiosFromDB() throws Exception {	
		Radio ra;
		List<Radio> raList = new ArrayList<Radio>();
		Cursor data = database.query(SQLiteDBHelper.TABLE_RADIOS, allColumns, null,
				null, null, null, SQLiteDBHelper.COLUMN_RATING);
		int id;
		String navn;
		String url;
		String slug;
		String country;
		if (data.moveToFirst()) {
			id = data.getInt(data.getColumnIndex(SQLiteDBHelper.COLUMN_ID));
			navn = data.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_NAME));
			url = data.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_RADIOURL));
			slug = data.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_SLUG));
			country = data.getString(data
					.getColumnIndex(SQLiteDBHelper.COLUMN_COUNTRY));
			ra = new Radio(id, navn, url, slug, country);
			raList.add(ra);

			while (data.moveToNext()) {
				id = data.getInt(data.getColumnIndex(SQLiteDBHelper.COLUMN_ID));
				navn = data
						.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_NAME));
				url = data.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_RADIOURL));
				slug = data
						.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_SLUG));
				country = data.getString(data
						.getColumnIndex(SQLiteDBHelper.COLUMN_COUNTRY));
				ra = new Radio(id, navn, url, slug, country);
				raList.add(ra);
			}
		}
		data.close();
		return raList;
	}

	// Selects all radios from the most table, and returns them in a list<Radio>
	public List<Radio> SelectMostRadiosFromDB() throws Exception {
		Radio ra;
		List<Radio> raList = new ArrayList<Radio>();
		int id;
		String navn;
		String url;
		String slug;
		String country;
		Cursor data2 = database
				.rawQuery(
						"SELECT r.* FROM most m, radios r WHERE r._id = m._id ORDER BY m.count DESC",
						null);
		if (data2.moveToFirst()) {

			id = data2.getInt(data2.getColumnIndex(SQLiteDBHelper.COLUMN_ID));
			navn = data2.getString(data2.getColumnIndex(SQLiteDBHelper.COLUMN_NAME));
			url = data2.getString(data2
					.getColumnIndex(SQLiteDBHelper.COLUMN_RADIOURL));
			slug = data2.getString(data2.getColumnIndex(SQLiteDBHelper.COLUMN_SLUG));
			country = data2.getString(data2
					.getColumnIndex(SQLiteDBHelper.COLUMN_COUNTRY));
			ra = new Radio(id, navn, url, slug, country);
			raList.add(ra);

			while (data2.moveToNext()) {
				id = data2.getInt(data2.getColumnIndex(SQLiteDBHelper.COLUMN_ID));
				navn = data2.getString(data2
						.getColumnIndex(SQLiteDBHelper.COLUMN_NAME));
				url = data2.getString(data2
						.getColumnIndex(SQLiteDBHelper.COLUMN_RADIOURL));
				slug = data2.getString(data2
						.getColumnIndex(SQLiteDBHelper.COLUMN_SLUG));
				country = data2.getString(data2
						.getColumnIndex(SQLiteDBHelper.COLUMN_COUNTRY));
				ra = new Radio(id, navn, url, slug, country);
				raList.add(ra);
			}
		}
		data2.close();
		return raList;
	}

	// Selects all radios from the favorites table, and returns them in a
	// list<Radio>
	public List<Radio> SelectFavRadiosFromDB() throws Exception {
		Radio ra;
		List<Radio> raList = new ArrayList<Radio>();
		int id;
		String navn;
		String url;
		String slug;
		String country;
		Cursor data2 = database.rawQuery("SELECT * FROM fav ORDER BY name ASC",
				null);
		if (data2.moveToFirst()) {

			id = data2.getInt(data2.getColumnIndex(SQLiteDBHelper.COLUMN_ID));
			navn = data2.getString(data2.getColumnIndex(SQLiteDBHelper.COLUMN_NAME));
			url = data2.getString(data2
					.getColumnIndex(SQLiteDBHelper.COLUMN_RADIOURL));
			slug = data2.getString(data2.getColumnIndex(SQLiteDBHelper.COLUMN_SLUG));
			country = data2.getString(data2
					.getColumnIndex(SQLiteDBHelper.COLUMN_COUNTRY));
			ra = new Radio(id, navn, url, slug, country);
			raList.add(ra);

			while (data2.moveToNext()) {
				id = data2.getInt(data2.getColumnIndex(SQLiteDBHelper.COLUMN_ID));
				navn = data2.getString(data2
						.getColumnIndex(SQLiteDBHelper.COLUMN_NAME));
				url = data2.getString(data2
						.getColumnIndex(SQLiteDBHelper.COLUMN_RADIOURL));
				slug = data2.getString(data2
						.getColumnIndex(SQLiteDBHelper.COLUMN_SLUG));
				country = data2.getString(data2
						.getColumnIndex(SQLiteDBHelper.COLUMN_COUNTRY));
				ra = new Radio(id, navn, url, slug, country);
				raList.add(ra);
			}
		}
		data2.close();
		return raList;
	}

	// Deletes all rows in the radios table
	public void DeleteDB() {
		dbHelper.getWritableDatabase().execSQL("DELETE FROM " + "radios" + ";");
	}

	// Updates the database version column in table userData with an already
	// collected value from a HTTP request "JSONparser.version"
	public void UpdateVersion() {
		ContentValues values = new ContentValues();
		values.put(SQLiteDBHelper.COLUMN_VERSION, JSONparser.version);
		database.update(SQLiteDBHelper.TABLE_USERDATA, values, null, null);
	}

	// Updates the database country column in table userData with an already
	// collected value from a HTTP request "JSONparser.country"
	public void UpdateCountry() {
		ContentValues values = new ContentValues();
		values.put(SQLiteDBHelper.COLUMN_COUNTRY, JSONparser.country);
		database.update(SQLiteDBHelper.TABLE_USERDATA, values, null, null);
	}

	// Checks if userData table is empty
	public boolean DoesEntryOfUserDataExist() {
		String sql = "SELECT radioActive,version FROM userData";
		Cursor data = database.rawQuery(sql, null);
		if (data.moveToFirst()) {
			data.close();
			return true;
		} else {
			data.close();
			return false;
		}
	}

	// Inserts received data into userData table.
	// Used if userData is empty.
	public void InsertIntouserData(double version, int id, String country) {
		ContentValues values = new ContentValues();
		values.put(SQLiteDBHelper.COLUMN_VERSION, version);
		values.put(SQLiteDBHelper.COLUMN_RADIOACTIVE, id);
		values.put(SQLiteDBHelper.COLUMN_COUNTRY, country);
		database.insert(SQLiteDBHelper.TABLE_USERDATA, null, values);
	}

	// Returns version from userData table as double
	public double GetVersion() {
		Cursor data = database.rawQuery("SELECT version FROM userData", null);
		double version = -1;
		if (data.moveToFirst()) {
			version = data.getDouble(data
					.getColumnIndex(SQLiteDBHelper.COLUMN_VERSION));
		}
		data.close();
		return version;
	}

	// Returns the country from userData table
	public String GetCountry() {
		Cursor data = database.rawQuery("SELECT country FROM userData", null);
		String country = null;
		if (data.moveToFirst()) {
			country = data.getString(data
					.getColumnIndex(SQLiteDBHelper.COLUMN_COUNTRY));
		}
		data.close();
		return country;
	}

	// Receives a ID and sets it as the active id (radioactive) in the userdata
	// table.
	// This is later used for playing a radio in the radioservice class.
	public void UpdateActiveID(int value) {
		ContentValues values = new ContentValues();
		values.put(SQLiteDBHelper.COLUMN_RADIOACTIVE, value);
		database.update(SQLiteDBHelper.TABLE_USERDATA, values, null, null);
	}

	// Returns the URL of the active radio station.
	// If favorite tab is selected, it gets the URL from fav table, else the
	// radios table.
	public String GetActiveURL() throws Exception {
		int radioActive = 0;
		String activeURL = null;
		Cursor cursor = database.rawQuery("SELECT radioActive FROM userData",
				null);
		if (cursor.moveToFirst()) {
			radioActive = cursor.getInt(cursor.getColumnIndex("radioActive"));
		}
		cursor.close();

		Cursor data;
		if (MainActivity.tabHost.getCurrentTab() == 2) {
			data = database.rawQuery("SELECT radioURL FROM fav WHERE _id = "
					+ radioActive, null);
		} else {
			data = database.rawQuery("SELECT radioURL FROM radios WHERE _id = "
					+ radioActive, null);
		}
		if (data.moveToFirst())
			activeURL = data.getString(data.getColumnIndex("radioURL"));
		data.close();
		return new String(mcrypt.decrypt(activeURL)).trim();
	}

	// returns the id og the active radio station
	public int GetActiveID() {
		int id = 0;
		Cursor cursor = database.rawQuery("SELECT radioActive FROM userData",
				null);
		if (cursor.moveToFirst()) {
			id = cursor.getInt(cursor
					.getColumnIndex(SQLiteDBHelper.COLUMN_RADIOACTIVE));
		}
		cursor.close();
		return id;
	}

	// Counts up the count in most table, for the received radio id
	// If id does not yet exists, its created in a new row
	public void OneUp(int id) {
		Cursor data = database.rawQuery("SELECT count FROM most WHERE _id = "
				+ id, null);
		if (data.getCount() > 0) {
			data.moveToFirst();
			int count = data.getInt(data.getColumnIndex(SQLiteDBHelper.COLUMN_COUNT));
			data.close();
			ContentValues values = new ContentValues();
			values.put(SQLiteDBHelper.COLUMN_COUNT, count + 1);
			String temp = "_id = " + id;
			database.update(SQLiteDBHelper.TABLE_MOST, values, temp, null);
		} else {
			ContentValues values = new ContentValues();
			values.put(SQLiteDBHelper.COLUMN_ID, id);
			values.put(SQLiteDBHelper.COLUMN_COUNT, 1);
			database.insert(SQLiteDBHelper.TABLE_MOST, null, values);
			data.close();
		}
	}

	// Inserts received radio into fav table
	// Fav table needs full radio information, to handle global radio stations
	// not present in radios table
	public void InsertToFav(Radio radio) throws Exception {
		ContentValues values = new ContentValues();
		values.put(SQLiteDBHelper.COLUMN_ID, radio.getId());
		values.put(SQLiteDBHelper.COLUMN_NAME, radio.getName());
		values.put(SQLiteDBHelper.COLUMN_RADIOURL, radio.getUrl());
		values.put(SQLiteDBHelper.COLUMN_SLUG, radio.getSlug());
		values.put(SQLiteDBHelper.COLUMN_COUNTRY, radio.getCountry());
		database.insert(SQLiteDBHelper.TABLE_FAV, null, values);
	}

	// Removed radio with received id from favorites
	public void RemoveFromFav(int id) {
		database.execSQL("DELETE FROM fav WHERE _id = " + id);
	}
	
	// Looks for a specific ID in the favorites table and return true if its there, and false if its not.
	public boolean DoesFavExists(int id) {
		boolean temp = false;
		Cursor data = database.rawQuery(
				"SELECT _id FROM fav WHERE _id = " + id, null);
		if (data.moveToFirst())
			temp = true;
		data.close();
		return temp;
	}
	
	// Returns the name of the last played/playing radio.
	// If on tab 3 (the favorite tab) it gets the name from the favorite table.
	// If not tab 3, it gets the name from the radios table.
	public String GetTheNameOfTheRadiostationThatIsPlaying() {
		int radioActive = 0;
		String temp = null;
		Cursor cursor = database.rawQuery("SELECT radioActive FROM userData",
				null);
		if (cursor.moveToFirst()) {
			radioActive = cursor.getInt(cursor.getColumnIndex("radioActive"));
		}
		cursor.close();

		Cursor data;
		if (MainActivity.tabHost.getCurrentTab() == 2) {
			data = database.rawQuery("SELECT name FROM fav WHERE _id = "
					+ radioActive, null);
		} else {
			data = database.rawQuery("SELECT name FROM radios WHERE _id = "
					+ radioActive, null);
		}
		if (data.moveToFirst())
			temp = data.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_NAME));
		data.close();
		return temp;
	}
	
	// Returns the ID of the last played/now playing radio station
	public int GetRadioActive() {
		int temp = 0;
		Cursor data = database.rawQuery("SELECT radioActive FROM userData",
				null);
		if (data.moveToFirst())
			temp = data
					.getInt(data.getColumnIndex(SQLiteDBHelper.COLUMN_RADIOACTIVE));
		data.close();
		return temp;
	}
	
	// Receives a string, and looks for matching names in the radios table, puts them in a list<Radio>, and returns the list.
	public List<Radio> SearchLocal(String s) throws Exception {
		List<Radio> list = new ArrayList<Radio>();
		Radio ra;
		Cursor data = database.rawQuery(
				"SELECT * FROM radios WHERE name LIKE '%" + s + "%'", null);
		int id;
		String navn;
		String url;
		String slug;
		String country;
		if (data.moveToFirst()) {
			id = data.getInt(data.getColumnIndex(SQLiteDBHelper.COLUMN_ID));
			navn = data.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_NAME));
			url = data.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_RADIOURL));
			slug = data.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_SLUG));
			country = data.getString(data
					.getColumnIndex(SQLiteDBHelper.COLUMN_COUNTRY));
			ra = new Radio(id, navn, url, slug, country);
			list.add(ra);

			while (data.moveToNext()) {
				id = data.getInt(data.getColumnIndex(SQLiteDBHelper.COLUMN_ID));
				navn = data
						.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_NAME));
				url = data.getString(data
						.getColumnIndex(SQLiteDBHelper.COLUMN_RADIOURL));
				slug = data
						.getString(data.getColumnIndex(SQLiteDBHelper.COLUMN_SLUG));
				country = data.getString(data
						.getColumnIndex(SQLiteDBHelper.COLUMN_COUNTRY));
				ra = new Radio(id, navn, url, slug, country);
				list.add(ra);
			}
		}
		data.close();
		return list;
	}
}